-- 统计视频类别热度Top10
-- 视频类别   类别热度 =  COUNT(videoId) GROUP BY 类别
SELECT 
category_name, 
COUNT(videoId) hot
FROM ( 
SELECT * FROM gulivideo_orc
    LATERAL VIEW explode(category) category as category_name
) t1 GROUP BY t1.category_name ORDER BY hot DESC LIMIT 10;


-- 统计出视频观看数最高的20个视频
-- 的所属类别以及类别包含Top20视频的个数
SELECT videoId,views FROM gulivideo_orc ORDER BY views LIMIT 20;

SELECT videoId,views,category FROM gulivideo_orc ORDER BY views LIMIT 20 
LATERAL VIEW explode(category) t_category as category_name;

SELECT DISTINCT(category_name),
count(videoId) OVER(PARTITION BY category_name  ) count_of_video
FROM 
(SELECT videoId,views,category_name FROM 
  (SELECT *
FROM gulivideo_orc  ORDER BY views LIMIT 20 ) t0 
LATERAL VIEW explode(category) t_category as category_name
) t1 ORDER BY count_of_video DESC;


SELECT t1.category_name,t1.top
FROM (
    SELECT videoId,views,category,category_name,ROW_NUMBER() OVER(PARTITION BY category_name ORDER BY views DESC) top
     FROM gulivideo_orc 
    LATERAL VIEW explode(category) t_category as category_name 
) t1
 WHERE t1.top <=10;

SELECT t2.* FROM
(
SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY t0.category_name ORDER BY t0.views DESC) top FROM
(
SELECT t0.*,t0.category_name
FROM  
(
SELECT videoId,views,category,category_name FROM 
 gulivideo_orc LATERAL VIEW explode(category) t_category as category_name
) t0 ) t1
) t2 WHERE t2.top <=10;

 
